package com.ldzy.webase.dao;

import com.ldzy.webase.entity.Comment;
import com.ldzy.webase.entity.Press;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author ：Lq
 * @version ：1.0
 * @date ：2022/10/25 21:06
 * @description ：t_press-咨询表-数据层
 */
public interface PressMapper {
    // 获取最新咨询列表，可根据(consultId咨询类别，status状态，uid用户编号)进行查询;也可不要参数，默认查询全部并根据最新发布时间排序
    // 默认根据publishDate发布日期字段进行降序排序
    // 可分页查询 curPage从第几条数据开始(pageNum - 1) * pageSize，pageSize每页显示的数量
    // 使用了left join左连接查询（左连接可以看做在内连接基表t_press的基础上，把左表中不满足ON条件的数据也显示出来，不满足为null，也会显示）
    @Select("<script>" +
            "select p.*,(select COUNT(*) from t_comment as c where c.pressId = p.pressId) as commentNum," +
            "(select COUNT(*) from t_presslike as plk where plk.pressId = p.pressId) as likeNum," +
            "u.username,u.avatar,con.consultImage,con.consultName " +
            "from t_press as p " +
            "left join t_user u on u.uid = p.uid " +
            "left join t_consult con on con.consultId = p.consultId " +
            "<where> " +
            "<if test=\"press.consultId != null\"> " +
            "and p.consultId=#{press.consultId} " +
            "</if>" +
            "<if test=\"press.status != null\"> " +
            "and p.status=#{press.status} " +
            "</if>" +
            "</where>" +
            "order by p.publishDate desc " +
            "<if test=\"curPage != null and pageSize != null\"> " +
            "LIMIT #{pageSize} OFFSET #{curPage} " +
            "</if>" +
            "</script>")
    List<Press> getPressAllByMore(@Param("press") Press press, @Param("curPage") Integer curPage, @Param("pageSize") Integer pageSize);

    // 获取24小时内热门的咨询列表，可根据(consultId咨询类别，status状态，uid用户编号)进行查询;也可不要参数，默认查询全部
    // 评论数、用户昵称、用户头像、咨询分类名称 是数据库表里没有的
    // 可分页查询 curPage从第几条数据开始(pageNum - 1) * pageSize，pageSize每页显示的数量
    // 使用了left join左连接查询（左连接可以看做在内连接基表t_press的基础上，把左表中不满足ON条件的数据也显示出来，不满足为null，也会显示）
    @Select("<script>" +
            "select p.*,(select COUNT(*) from t_comment as c where c.pressId = p.pressId) as commentNum," +
            "(select COUNT(*) from t_presslike as plk where plk.pressId = p.pressId) as likeNum," +
            "u.username,u.avatar,con.consultImage,con.consultName " +
            "from t_press as p " +
            "left join t_user u on u.uid = p.uid " +
            "left join t_consult con on con.consultId = p.consultId " +
            "<where> " +
            "<if test=\"press.consultId != null\"> " +
            "and p.consultId=#{press.consultId} " +
            "</if>" +
            "<if test=\"press.status != null\"> " +
            "and p.status=#{press.status} " +
            "</if>" +
            "and p.publishDate >=(NOW() - interval 24 hour) " + // 获取24小时内的数据
            "</where>" +
            "order by " + // 排序，当数值相同时会进行下一个排序的序号，顺序为：1.根据阅读数排序 2.根据评论数排序 3.根据点赞数排序 4.根据时间排序
//            "p.readNum desc," +
            "commentNum desc," +
            "likeNum desc," +
            "p.publishDate desc " +
            "<if test=\"curPage != null and pageSize != null\"> " +
            "LIMIT #{pageSize} OFFSET #{curPage} " +
            "</if>" +
            "</script>")
    List<Press> getPressByInterval24HourHot(@Param("press") Press press, @Param("curPage") Integer curPage, @Param("pageSize") Integer pageSize);

    // 获取历史最热门的咨询列表，可根据(consultId咨询类别，status状态，uid用户编号)进行查询;也可不要参数，默认查询全部
    // 评论数、用户昵称、用户头像、咨询分类名称 是数据库表里没有的
    // 可分页查询 curPage从第几条数据开始(pageNum - 1) * pageSize，pageSize每页显示的数量
    // 使用了left join左连接查询（左连接可以看做在内连接基表t_press的基础上，把左表中不满足ON条件的数据也显示出来，不满足为null，也会显示）
    @Select("<script>" +
            "select p.*,(select COUNT(*) from t_comment as c where c.pressId = p.pressId) as commentNum," +
            "(select COUNT(*) from t_presslike as plk where plk.pressId = p.pressId) as likeNum," +
            "u.username,u.avatar,con.consultImage,con.consultName " +
            "from t_press as p " +
            "left join t_user u on u.uid = p.uid " +
            "left join t_consult con on con.consultId = p.consultId " +
            "<where> " +
            "<if test=\"press.consultId != null\"> " +
            "and p.consultId=#{press.consultId} " +
            "</if>" +
            "<if test=\"press.status != null\"> " +
            "and p.status=#{press.status} " +
            "</if>" +
            "</where>" +
            "order by " + // 排序，当数值相同时会进行下一个排序的序号，顺序为：1.根据阅读数排序 2.根据评论数排序 3.根据点赞数排序 4.根据时间排序
//            "p.isTop desc," +
//            "p.readNum desc," +
            "commentNum desc," +
            "likeNum desc," +
            "p.publishDate desc " +
            "<if test=\"curPage != null and pageSize != null\"> " +
            "LIMIT #{pageSize} OFFSET #{curPage} " +
            "</if>" +
            "</script>")
    List<Press> getPressByMostHot(@Param("press") Press press, @Param("curPage") Integer curPage, @Param("pageSize") Integer pageSize);

    // 获取最新评论时间的咨询，可根据(consultId咨询类别，status状态，uid用户编号)进行查询;也可不要参数，默认查询全部
    // 评论数、最新评论时间、用户昵称、用户头像、咨询分类名称 是数据库表里没有的
    // 根据newCommentDate咨询内所有评论的最大MAX(发布时间)字段进行降序排序
    // 可分页查询 curPage从第几条数据开始(pageNum - 1) * pageSize，pageSize每页显示的数量
    // 使用了left join左连接查询（左连接可以看做在内连接基表t_press的基础上，把左表中不满足ON条件的数据也显示出来，不满足为null，也会显示）
    @Select("<script>" +
            "select p.*,(select COUNT(*) from t_comment as c where c.pressId = p.pressId) as commentNum," +
            "(select COUNT(*) from t_presslike as plk where plk.pressId = p.pressId) as likeNum," +
            "(select MAX(commentDate) from t_comment as c where c.pressId = p.pressId) as newCommentDate," +
            "u.username,u.avatar,con.consultImage,con.consultName " +
            "from t_press as p " +
            "left join t_user u on u.uid = p.uid " +
            "left join t_consult con on con.consultId = p.consultId " +
            "<where> " +
            "<if test=\"press.consultId != null\"> " +
            "and p.consultId=#{press.consultId} " +
            "</if>" +
            "<if test=\"press.status != null\"> " +
            "and p.status=#{press.status} " +
            "</if>" +
            "</where>" +
            "order by " + // 排序，当数值相同时会进行下一个排序的序号，顺序为：1.根据时间排序 2.根据浏览器排序 3.根据评论数排序 4.根据点赞数排序
            "newCommentDate desc " +
            "<if test=\"curPage != null and pageSize != null\"> " +
            "LIMIT #{pageSize} OFFSET #{curPage} " +
            "</if>" +
            "</script>")
    List<Press> getPressByNewCommentDate(@Param("press") Press press, @Param("curPage") Integer curPage, @Param("pageSize") Integer pageSize);

    // 根据pressId，获取咨询的详情信息
    @Select("<script>" +
            "select p.*,(select COUNT(*) from t_comment as c where c.pressId = p.pressId) as commentNum," +
            "(select COUNT(*) from t_presslike as plk where plk.pressId = p.pressId) as likeNum," +
            "u.username,u.avatar,con.consultImage,con.consultName,con.isContact " +
            "<if test=\"uid != null and uid != 0\"> " +
            ",(select COUNT(*) from t_presslike where pressId=p.pressId and uid=#{uid}) as likeStatus " +
            "</if>" +
            "from t_press as p " +
            "left join t_user u on u.uid = p.uid " +
            "left join t_consult con on con.consultId = p.consultId " +
            "<where> " +
            "<if test=\"pressId != null\"> " +
            "and p.pressId=#{pressId} " +
            "</if>" +
            "</where>" +
            "</script>")
    Press getPressByPressId(Integer pressId, Integer uid);

    // 获取咨询列表的总数，也可根据24小时内的咨询列表获取总数
    @Select("<script>" +
            "select COUNT(*) as total from t_press as p " +
            "<where> " +
            "<if test=\"is24Hour\"> " +
            "and p.publishDate >=(NOW() - interval 24 hour) " +
            "</if>" +
            "<if test=\"press.consultId != null\"> " +
            "and p.consultId=#{press.consultId} " +
            "</if>" +
            "<if test=\"press.status != null\"> " +
            "and p.status=#{press.status} " +
            "</if>" +
            "<if test=\"press.uid != null\"> " +
            "and p.uid=#{press.uid} " +
            "</if>" +
            "</where> " +
            "</script>")
    int getPressTotalOrBy24Hour(@Param("press") Press press, @Param("is24Hour") Boolean is24Hour);

    // 发布资讯/话题 (成功返回1，否则返回0)
    @Insert("insert into t_press(consultId,uid,content,pressImages,qqNumber,wxNumber,phoneNumber,publishProvince) " +
            "values(#{consultId},#{uid},#{content},#{pressImages}," +
            "#{qqNumber},#{wxNumber},#{phoneNumber},#{publishProvince})")
    int addPress(Press press);

    // 咨询点赞操作-数据库中有使用联合唯一索引，所以会有重复的点赞会自动排除
    @Insert("insert into t_pressLike(pressId,uid) values(#{pressId},#{uid})")
    int addPressLike(Integer pressId, Integer uid);

    // 咨询取消点赞操作-数据库中有使用联合唯一索引，所以会有重复的点赞会自动排除
    @Delete("delete from t_pressLike where pressId=#{pressId} and uid=#{uid}")
    int pressCancelLike(Integer pressId, Integer uid);

    // 获取资讯/话题评论列表,留言数，评论时间，点赞数，可根据(pressId咨询编号)进行查询;也可不要参数，默认查询全部。
    // 可分页查询 curPage从第几条数据开始(pageNum - 1) * pageSize，pageSize每页显示的数量
    @Select("<script>" +
            "select com.*,u.username,u.avatar " +
            "from t_comment as com " +
            "left join t_user u on u.uid = com.uid " +
            "<where>" +
            "<if test=\"comment.pressId != null\"> " +
            "and com.pressId=#{comment.pressId} " +
            "</if>" +
            "</where>" +
            "order by " +       // 降序排序：当数值相同时会进行下一个排序的序号，顺序为：1.根据点赞数+评论数排序 2.根据评论数排序 3.根据点赞数排序 4.根据评论时间排序
            "com.likeNum desc," +
            "com.commentDate desc " +
            "<if test=\"curPage != null and pageSize != null\"> " +
            "LIMIT #{pageSize} OFFSET #{curPage} " +
            "</if>" +
            "</script>")
    List<Comment> getCommentByPressId(@Param("comment") Comment comment, @Param("curPage") Integer curPage, @Param("pageSize") Integer pageSize);

    // 发表评论 (成功返回1，否则返回0)
    @Insert("insert into t_comment(pressId,uid,content,contentImages,commentProvince) " +
            "values(#{pressId},#{uid},#{content},#{contentImages},#{commentProvince})")
    int addComment(Comment comment);

    // 搜索（全文检索）- 获取总数total
    @Select("select COUNT(*) as total " +
            "from t_press " +
            "where MATCH(content) AGAINST(#{searchValue} IN NATURAL LANGUAGE MODE);")
    int getSearchPressTotal(@Param("searchValue") String searchValue);

    // 搜索（全文检索）
    // 全文索引和 like 查询对比，有以下优点：
    // like 只是进行模糊匹配，全文索引却提供了一些语法语义的查询功能，会将要查的字符串进行分词操作，这决定于 Mysql 的词库。
    // 全文索引可以自己设置词语的最小、最大长度，要忽略的词，这些都是可以设置的。
    // 用全文索引去某个列查一个字符串，会返回匹配度，可以理解为匹配的关键字个数，是个浮点数。
    @Select("<script>" +
            "select p.*,MATCH(p.content) AGAINST (#{searchValue}) as content_score," +
            "(select COUNT(*) from t_comment as c where c.pressId = p.pressId) as commentNum," +
            "(select COUNT(*) from t_presslike as plk where plk.pressId = p.pressId) as likeNum," +
            "u.username,u.avatar,con.consultImage,con.consultName " +
            "from t_press as p " +
            "left join t_user u on u.uid = p.uid " +
            "left join t_consult con on con.consultId = p.consultId " +
            "<where>" +
            "MATCH(p.content) AGAINST(#{searchValue} IN NATURAL LANGUAGE MODE) " +
            "</where>" +
            "order by " +
            "content_score desc," +
            "p.publishDate desc," +
            "commentNum desc " +
            "<if test=\"curPage != null and pageSize != null\"> " +
            "LIMIT #{pageSize} OFFSET #{curPage} " +
            "</if>" +
            "</script>")
    List<Press> searchPress(@Param("searchValue") String searchValue, @Param("curPage") Integer curPage, @Param("pageSize") Integer pageSize);
}
